package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;

import util.GetTime;

public class PhotoCommentDAO {
	
	public PhotoComment insert(PhotoComment pc){
		Connection conn = ConnectionManager.getConnection();
//		String sql1 = "insert into photocomment(pbno, mid, pccomment, pcdate, iconno) values(?,?,?,now(),?)";
		String sql1 = "insert into photocomment(pcno,pbno, mid, pccomment, pcdate, iconno) values(pcseq.NEXTVAL,?,?,?,?,?)";
		String sql2 = "select pcno,pbno,mid,pccomment,pcdate,iconno from photocomment where pcno=(select max(pcno) from photocomment)";
		try {
			///////////////////////////////////////////////
			PreparedStatement pstmt = conn.prepareStatement(	sql1);
			PreparedStatement pstmt2 = conn.prepareStatement(sql2);
			
			pstmt.setInt(1, pc.getPbno());
			pstmt.setString(2, pc.getMid());
			pstmt.setString(3, pc.getPccomment());
			pstmt.setTimestamp(4, GetTime.nowTime);
			pstmt.setInt(5, pc.getIconno());
//			pstmt.setInt(4, pc.getIconno());
			
			if(pstmt.executeUpdate()>0){
				ResultSet rs = pstmt2.executeQuery();
				PhotoComment photoComment = new PhotoComment();
				while(rs.next()){					
					photoComment.setPcno(rs.getInt(1));
					photoComment.setPbno(rs.getInt(2));
					photoComment.setMid(rs.getString(3));
					photoComment.setPccomment(rs.getString(4));
					photoComment.setPcdate(rs.getString(5));
					photoComment.setIconno(rs.getInt(6));
				}
				return photoComment;	
			}
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
		return null;
	}
	
	//Delete One of Comment per PhotoComment Number
	public boolean delete(int pcno){
		Connection conn = ConnectionManager.getConnection();
		try{
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("delete from photocomment where pcno='" + pcno + "'");
			return true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
	}
	
	//Delete all of Comment per PhotoBoard Number
	public boolean deletePerPbno(int pbno){
		Connection conn = ConnectionManager.getConnection();
		try{
			Statement stmt = conn.createStatement();
			stmt.executeUpdate("delete from photocomment where pbno='" + pbno + "'");
			return true;
		}catch(Exception e){
			e.printStackTrace();
			return false;
		}finally{
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
	}
	
	public boolean update(PhotoComment pc){
		Connection conn = ConnectionManager.getConnection();
		try{
			PreparedStatement pstmt = conn.prepareStatement(
				"update photocomment set pbno=?,mid=?,pccomment=?,iconno=? where pcno=?");
			pstmt.setInt(1, pc.getPbno());
			pstmt.setString(2, pc.getMid());
			pstmt.setString(3, pc.getPccomment());
			pstmt.setInt(4, pc.getIconno());
			pstmt.setInt(5, pc.getPcno());
			pstmt.executeUpdate();
			return true;
		}catch(Exception e){
			return false;
		}finally{
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}	
	}
	
	//get List of All PhotoComment
	public Vector<PhotoComment> getList(){
		Vector<PhotoComment> photocomments = new Vector<PhotoComment>();
		Connection conn = ConnectionManager.getConnection();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from photocomment");
			while(rs.next()){
				PhotoComment photocomment = new PhotoComment();
				photocomment.setPcno(rs.getInt(1));
				photocomment.setPbno(rs.getInt(2));
				photocomment.setMid(rs.getString(3));
				photocomment.setPccomment(rs.getString(4));
				photocomment.setPcdate(rs.getString(5));
				photocomment.setIconno(rs.getInt(6));
				photocomments.add(photocomment);
			}
			rs.close();
			stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
		return photocomments;
	}
	
	//get PhotoComment List per Photoboard Number
	public Vector<PhotoComment> getListPerPbno(int pbno){
		Vector<PhotoComment> photocomments = new Vector<PhotoComment>();
		Connection conn = ConnectionManager.getConnection();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from photocomment where pbno='" + pbno + "'");
			while(rs.next()){
				PhotoComment photocomment = new PhotoComment();
				photocomment.setPcno(rs.getInt(1));
				photocomment.setPbno(rs.getInt(2));
				photocomment.setMid(rs.getString(3));
				photocomment.setPccomment(rs.getString(4));
				photocomment.setPcdate(rs.getString(5).substring(0, 19));
				photocomment.setIconno(rs.getInt(6));
				photocomments.add(photocomment);
			}
			rs.close();
			stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
		return photocomments;
	}
	
	public PhotoComment getPhotocomment(int pcno){
		PhotoComment photocomment = null;
		Connection conn = ConnectionManager.getConnection();
		try{
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery("select * from photocomment where pcno='" + pcno + "'");
			rs.next();
			photocomment = new PhotoComment();
			photocomment.setPcno(rs.getInt(1));
			photocomment.setPbno(rs.getInt(2));
			photocomment.setMid(rs.getString(3));
			photocomment.setPccomment(rs.getString(4));
			photocomment.setPcdate(rs.getString(5));
			photocomment.setIconno(rs.getInt(6));
			rs.close();
			stmt.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
		return photocomment;
	}
	
	public int getMaxPcno(PhotoComment pc){
		int pcno=0;
		Connection conn = ConnectionManager.getConnection();
		try{
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select max(pcno) from photocomment where mid='"+pc.getMid()+"'");
			if(rs.next()){
				pcno = rs.getInt(1);
			}
			rs.close();
			st.close();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			if(conn!=null)try{conn.close();}catch(Exception e){}
		}
		return pcno;
	}
	
}
